﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace ISS.CaseStudy.DBUtility
{
    public static class Database
    {
        private static string _connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();

       
        public static SqlDataReader SpExceuteReader(string commandText)
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                SqlDataReader dr = null;
                try
                {
                    SqlCommand cmd = new SqlCommand(commandText, cn);
                    cn.Open();
                    dr = cmd.ExecuteReader();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
                return dr;
            }
        }

        public static int SpExecuteNonQuery(string commandText)
        {
           return SpExecuteNonQuery(commandText, null);
        }
        public static int SpExecuteNonQuery(string commandText, SqlParameter[] paramCollection)
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                int i = 0;
                try
                {
                    SqlCommand cmd = new SqlCommand(commandText, cn);
                    if (paramCollection != null)
                    {
                        foreach (SqlParameter param in paramCollection)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    cn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
                return i;
            }
        }

        public static DataSet SpExecuteDataSet(string commandText)
        {
            return SpExecuteDataSet(commandText, null);
        }
        public static DataSet SpExecuteDataSet(string commandText, SqlParameter[] paramCollection)
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(commandText, cn);
                if (paramCollection != null)
                {
                    foreach (SqlParameter param in paramCollection)
                    {
                        adapter.SelectCommand.Parameters.Add(param);
                    }
                }
                DataSet ds = new DataSet();
                adapter.Fill(ds, "dataTable");
                cn.Close();
                return ds;
            }
        }

        public static string SpExecuteScalar(string commandText)
        {
            return SpExecuteScalar(commandText, null);
        }
        public static string SpExecuteScalar(string commandText, SqlParameter[] paramCollection)
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                string s = string.Empty;
                try
                {
                    SqlCommand cmd = new SqlCommand(commandText, cn);
                    if (paramCollection != null)
                    {
                        foreach (SqlParameter param in paramCollection)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    cn.Open();
                    s = cmd.ExecuteScalar().ToString();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
                return s;
            }
          
        }


        public static int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, null);
        }
        public static int ExecuteNonQuery(string commandText, SqlParameter[] paramCollection)
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                int i = 0;
                try
                {
                    SqlCommand cmd = new SqlCommand(commandText, cn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (paramCollection != null)
                    {
                        foreach (SqlParameter param in paramCollection)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    cn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
                return i;
            }
        }

        public static DataSet ExecuteDataSet(string commandText)
        {
            return ExecuteDataSet(commandText, null);
        }
        public static DataSet ExecuteDataSet(string commandText, SqlParameter[] paramCollection)
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(commandText, cn);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

                //SqlParameter p1 = new SqlParameter();
                //p1.Direction = ParameterDirection.Output;


                //ErrorID = p1.Value;

                if (paramCollection != null)
                {
                    foreach (SqlParameter param in paramCollection)
                    {
                        adapter.SelectCommand.Parameters.Add(param);
                    }
                }
                DataSet ds = new DataSet();
                adapter.Fill(ds, "dataTable");
                cn.Close();
                return ds;
            }
        }

    }
}
